<%@page trimDirectiveWhitespaces="true"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
 <%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page language="java" import="java.lang.*" %>
<%@ page import = "java.sql.SQLException" %>
<%@ page import = "com.seniorproject.aims.*" %>
<%@ page import = "java.util.List" %>
<%@page import="java.util.ArrayList"%>
<%@ page import = "org.codehaus.jackson.JsonGenerationException" %>
<%@ page import = "org.codehaus.jackson.map.JsonMappingException" %>
<%@ page import = "org.codehaus.jackson.map.ObjectMapper" %>
<%@ page import = "java.io.IOException" %>
<%@ page import = "java.io.File" %>
<%@ page import = "java.io.FileInputStream" %>
<%@ page import = "org.apache.poi.ss.usermodel.Cell" %>
<%@ page import = "org.apache.poi.ss.usermodel.Row" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import = "java.util.Iterator" %>
<%@ page import = "org.apache.poi.hssf.usermodel.HSSFDateUtil" %>
<%@ page import = "java.text.SimpleDateFormat" %>
<%@ page import = "java.util.Date" %>
<%@ page import = "java.util.Properties" %>
<%@ page import = "java.io.InputStream" %>
<%@ page import = "java.sql.PreparedStatement" %>




<% 
	//get session
		String strUser = String.valueOf(session.getAttribute("sUser"));
		HttpSession htp_session = request.getSession();
		
		// check session
		if (htp_session == null || htp_session.getAttribute("sUser") == null) {
		    // Forward the control to login.jsp if authentication fails or session expires
		    request.getRequestDispatcher("/login.jsp").forward(request,
		        response);
		}
		if(!"admin".equals(htp_session.getAttribute("sUser"))){%>
			<script>alert("This user dont have PERMISSION to access this zone.");</script>
			<meta HTTP-EQUIV="Refresh" CONTENT="0; URL=system_select.jsp"> 	
	<%}
	
//set Database Connection
	String hostProps = "";
	String usernameProps  = "";
	String passwordProps  = "";
	String databaseProps = "";
	
	try {
		//get current path
		ServletContext servletContext = request.getSession().getServletContext();
		
		InputStream input = servletContext.getResourceAsStream("/properties/connectDB.properties");
		Properties props = new Properties();
		
		props.load(input);

		hostProps  = props.getProperty("host");
		usernameProps  = props.getProperty("username");
		passwordProps  = props.getProperty("password");
		databaseProps = props.getProperty("database");
	} catch (Exception e) { 
		out.println(e);  
	}
	
	// connect database
	Connection connect = null;		
	try {
		Class.forName("com.mysql.jdbc.Driver");
	
		connect =  DriverManager.getConnection("jdbc:mysql://" + hostProps  + "/" + databaseProps +
				"?user=" + usernameProps  + "&password=" + passwordProps + "&characterEncoding=tis620" );
	
		if(connect != null){
			System.out.println("Database Connect Sucesses.");
		} else {
			System.out.println("Database Connect Failed.");	
		}

	} catch (Exception e) {
		out.println(e.getMessage());
		e.printStackTrace();
	}
	




		System.out.println("Active lecturer_import_background !!");
		String file_pathx = request.getParameter("file_path");
		System.out.println("File_path background:"+file_pathx);
		String title_th = "";
		String lecturer_id ="";
		String title_en="";
		String name_th="";
		String edu_position="";
		String management_position="";
		String edu_position_id="";
		String management_position_id="";
		String surname_th="";
		String name_en="";
		String surname_en="";
		String gender="";
		String birthdate = null;
		String tel="";
		String address="";
		String email="";
		String facebook="";
		String full_name_id="";
		String status="ปกติ";
		String faculty_id="1";
		String department_str_in="";
		String department_id="";
		
		
        int i = 0;
        int num_row = 0;
        int num_col  = 0;
		int full_name_added = 0;
		int people_added = 0 ;
		int lecturer_added = 0 ;
		
		
		List<String> fail_list = new ArrayList<String>();
		List<String> error_log = new ArrayList<String>();
		
		java.util.Date dt = new java.util.Date();

		java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd",java.util.Locale.US);

		String date_now = sdf.format(dt);
		
		int count = 0;
		

			String[] header_array = new  String[]{"คำนำหน้า","ชื่อ","นามสกุล","ชื่อ(ภาษาอังกฤษ)",
												  "นามสกุล(ภาษาอังกฤษ)","เพศ","วันเกิด","เบอร์โทรศัพท์",
												  "ที่อยู่","ตำแหน่งทางวิชาการ","ตำแหน่งบริหาร","E-mail","Facebook","ภาควิชา"};
			
			System.out.println("Header_array size : "+ header_array.length);

		
		try{
			
			FileInputStream file = new FileInputStream(new File(file_pathx));
			XSSFWorkbook workbook = new XSSFWorkbook(file);
			 
            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);
 
            //Iterate through each rows one by one
            Iterator<Row> rowIterator = sheet.iterator();
            
            Iterator<Row> headerIt_row_check = sheet.iterator();
            Row row_check = headerIt_row_check.next();
            Iterator<Cell> headerIt_cell_check = row_check.cellIterator();

            while (rowIterator.hasNext())
            {	
            	System.out.println(" //////New Row/////  ");
            	System.out.println("Row:"+num_row);
            	if(count==0)
            	{
            		while(headerIt_cell_check.hasNext()){
            			Cell header_cell = headerIt_cell_check.next();
            			   switch (header_cell.getCellType())
                           {
                               case Cell.CELL_TYPE_NUMERIC:
                                   break;
                               case Cell.CELL_TYPE_STRING:
                                  // System.out.print(header_cell.getStringCellValue()+ " ");
                                  	 System.out.println(header_array[i]+":"+header_cell.getStringCellValue());
                                   	 if(header_array[i].equals(header_cell.getStringCellValue()))
                                   	 {
                                   		 System.out.println("Pass:" + i);
                                   	 }
                                   	 else{
                                   		 
                                   		throw new Exception();
                                   		
                                   	 }
                                   	 
                                   break;
                           }// end switch
            				i++;
            		}// end headerIt_cell_check.hasNext()
            		System.out.println("");
            		count = count +1;
            		rowIterator.next();
            		System.out.println("////////// END Header////////////");
            	}// end if(count==0)
            	
            	  
            	try{	
		            	Row row = rowIterator.next();
						
		                	// rowIterator.next();
		             	
		                	//For each row, iterate through all the columns
		             	   Iterator<Cell> cellIterator = row.cellIterator();
		             	
		                while (cellIterator.hasNext())
		                {
		                	
		                    Cell cell = cellIterator.next();
		                    //Check the cell type and format accordingly
		                   	switch(num_col){
		                   		case 0 :
				      					// title_th
				      					title_th = cell.getStringCellValue();
				      					title_th.replaceAll("\\s+","");
				      					System.out.println("title_th:"+title_th);
		                   			break;
		                   			
		                   			
		                   		case 1 :
		                   				//name_th
		                   				name_th = cell.getStringCellValue();
		                   				name_th.replaceAll("\\s+","");
		                   				System.out.println("name_th:"+name_th);
		                   			break;
		                   		case 2 :
		                   				//surname_th
		                   				surname_th = cell.getStringCellValue();
		                   				surname_th.replaceAll("\\s+","");
		                   				System.out.println("surname_th:"+surname_th);
		                   			break;
		                   		case 3 :
		                   				// name_en 
		                   				name_en  = cell.getStringCellValue();  
		                   				name_en.replaceAll("\\s+","");
		                   				System.out.println("name_en:"+name_en);
		                   			break;
		                   		case 4 :
		                   				// surname_en
		                   				surname_en = cell.getStringCellValue();
		                   				surname_en.replaceAll("\\s+","");
		                   				System.out.println("surname_en:"+surname_en);
		                   			break;
		                   		case 5 :
		                   				//gender 
		                   				gender = cell.getStringCellValue();
		                   				gender.replaceAll("\\s+","");
		                   				System.out.println("gender:"+gender);
		                   			break;
		                   		case 6 :
		                   			//birthdate 
		                   			if (HSSFDateUtil.isCellDateFormatted(cell)) {
									
		                              Date birthdate_in=  cell.getDateCellValue();
		                              int year_be = birthdate_in.getYear();
		                              birthdate_in.setYear(year_be-543);         
		                              String bd_string = birthdate_in.toString();
		                            
		                          
		                          	  birthdate =  "'"+sdf.format(birthdate_in)+"'";
		                          	  
		                              
		                            }
		                   				System.out.println("Case 6"+birthdate);
		                   			break;
		                   		case 7 :
		                   			//tel
		                   				tel = String.valueOf(cell.getStringCellValue());
		                   				tel.replaceAll("\\s+","");
			           					System.out.println("tel:"+tel);
		                   			break;
		                   		case 8 :
		                   			//address
		                   				address = cell.getStringCellValue();
		                   				address.replaceAll("\\s+","");
		                   				System.out.println("address:"+address);
		                   			break;
		                   		case 9:
		                   				edu_position = cell.getStringCellValue();
		                   				edu_position.replaceAll("\\s+","");
		                   				System.out.println("edu_position:"+edu_position);
		                   		      				
		                 			break;
		                   		case 10:
		                   				
			                   			management_position = cell.getStringCellValue();
			                   			management_position.replaceAll("\\s+","");
		    	           				System.out.println("management_position:"+management_position);           			
		                   			
		                   			break;
		                   	
		                   		case 11 :
		                   			//E-mail
		                   					
		                   					email = cell.getStringCellValue();
		                   					email.replaceAll("\\s+","");
		                   				
		                   				System.out.println("email:"+email);
		                   			break;
		                   		case 12 :
		                   				facebook = cell.getStringCellValue();
		                   				facebook.replaceAll("\\s+","");
		                   				System.out.println("facebook:"+facebook);
		                   			break;
		                   		case 13:
		                   				department_str_in = cell.getStringCellValue();
		                   				department_str_in.replaceAll("\\s+","");
		                   				System.out.println("department_str_in"+department_str_in);
		                   			
		                   			break;
		                   			
		                   	}
		                    
		                    num_col++;
		                }// end cellIterator.hasNext();
		                num_col=0;
		                num_row++;
		                
		                   			//Start import process
		            			System.out.println("Phase1");
						        System.out.println("name_th:"+name_th);
								System.out.println("surname_th:"+surname_th);
							    String key_index="";
							    
								
								
				                ///////////////////Phase 1 // Check Existing data in People table 
								ResultSet rs_people =	connect.createStatement().executeQuery(" SELECT P.index "
																				+ " FROM people P, full_name N "
																				+ " WHERE P.index=N.index "
																				+ " AND N.name_th='"+name_th+"'"
																				+ " AND N.surname_th='"+surname_th+"'");
				                   
				               
						                    
								rs_people.last();
								if(rs_people.getRow() == 1) {	
								
									
									key_index= rs_people.getString("index");	 
									System.out.println("Phase 2");
									System.out.println("Found in people table with index:"+key_index);
									System.out.println("edu_position:"+edu_position+":");
									//existing in people
									
											///////////////////Phase 2 Check education_postition 
											String sql_cmd_phase_2 = " SELECT education_position_id "
													+ " FROM education_position "
													+ " WHERE education_position_name='"+edu_position+"'";
											try{
												ResultSet rs_edu =	connect.createStatement().executeQuery(" SELECT education_position_id "
														+ " FROM education_position "
														+ " WHERE education_position_name='"+edu_position+"'");
												
												rs_edu.last();
												if(rs_edu.getRow() == 1) {
													
													edu_position_id =  rs_edu.getString("education_position_id");
													
													
													
													
												}else{
													//education_position not found !!
													System.out.println("edu_pos not found :" + sql_cmd_phase_2);
													throw new Exception();
													
													
												}
											}catch(Exception io){
												System.out.println("Phase 2 Error:"+io);
												error_log.add("row:"+num_row+": Phase 2 Error :"+io+" : "+ sql_cmd_phase_2);
											}
											////////////////////////////////////////////////////
											/////////////////Phase 3 Check Management_position
											String sql_cmd_phase_3 = " SELECT management_position_id "
													+ " FROM management_position "
													+ " WHERE management_position_name='"+management_position+"'";
											try{
													System.out.println("Phase 3 Check Management Position");
													System.out.println("management_position:"+management_position+":");
													if(("-").equals(management_position))
													{
														management_position_id = null;
														
														
													}else{
														//Get Managemenr_position_id
														ResultSet rs_man =	connect.createStatement().executeQuery(" SELECT management_position_id "
																+ " FROM management_position "
																+ " WHERE management_position_name='"+management_position+"'");
														
														rs_man.last();
														if(rs_man.getRow() == 1) {
															
															management_position_id =  rs_man.getString("management_position_id");
														}else{
															// management_position not found
															System.out.println("Phase 3 man_pos not found :"+sql_cmd_phase_3);	
															throw new Exception();
														}
														
														
														
														
													}
											}catch(Exception d){
												
												System.out.println("Phase 3 Error:"+d);
												error_log.add("row:"+num_row+": Phase 3 Error :"+d+":"+sql_cmd_phase_3);
												
											}
											////////////////////END Phase 3//////////////////
											//Phase 4 get department_id from department table
											System.out.println("Phase 4");
											System.out.println("depart_manet_str_in:"+department_str_in);
											try{
												System.out.println("Phase 4 department_str_in:"+department_str_in);
												ResultSet rs_department = connect.createStatement().executeQuery(" SELECT department_id"
														+ " FROM department "
														+ " WHERE department_name_short='" + department_str_in +"'" );
												rs_department.last();
												if(rs_department.getRow() == 1) {
													department_id  = rs_department.getString("department_id");
													
													System.out.println("Phase 4 Found Department_id:"+department_id);
													
												}else{
													System.out.println("Phase 4 error");
													
													throw new Exception();
														
													
												}
												
												
											}catch(Exception q){ 
												System.out.println("Error occer at Phase 4:"+q);
												error_log.add("row:"+num_row+": Phase 4 Error");
											}
											
											//End Phase 4
											///////////Phase 5 Add Lecturer
											String sql_cmd_phase_5 = " INSERT INTO `lecturer`(`index`,`faculty_id`,`department_id`,"+
													" `status`, `education_position_id`,`management_position_id`)"+
													" VALUES ('"+key_index+"', "+faculty_id+", "+department_id+","+
													" '"+status+"', "+edu_position_id+", "+management_position_id+")";
											try{
												
												System.out.println("key_index:"+key_index);
												System.out.println("faculty_id:"+faculty_id);
												System.out.println("department_id:"+department_id);
												System.out.println("status:"+status);
												System.out.println("edu_pos_id:"+edu_position_id);
												System.out.println("man_pos_id:"+management_position_id);
												
												String sql = " INSERT INTO `lecturer`(`index`,`faculty_id`,`department_id`,"+
														" `status`, `education_position_id`,`management_position_id`)"+
														" VALUES ('"+key_index+"', "+faculty_id+", "+department_id+","+
														" '"+status+"', "+edu_position_id+", "+management_position_id+")";
												connect.createStatement().executeUpdate(sql);
												
												// log file
												String log = "admin import lecturer";						
												Log.writeAdminFile(log);
												Log.writeAdminFile(sql);
														
												System.out.println("Phase 5 Success");
												lecturer_added ++;
														
												}catch(Exception nm){
												
													System.out.println("Phase 5 error:"+nm);
													error_log.add("row:"+num_row+": Phase 5 Error:"+nm+": "+sql_cmd_phase_5);
																								
												}
												///////////////////End Phase 5		
											
									
										
									
								}else{
											///////not existing in people
											////////////////Phase A Add Full_name////
											key_index = KeyGen.genTimeStamp();
											
											System.out.println("Phase A");
											System.out.println("key_index:"+key_index);
											System.out.println("Data before create full_name query");		
											System.out.println(title_th);
											System.out.println(title_en);
											System.out.println(name_th);
											System.out.println(name_en);
											System.out.println(surname_th);
											System.out.println(surname_en);
											System.out.println("gender:"+gender+":");
											System.out.println(date_now.toString());
											
											try{
												String sql = (" INSERT INTO `full_name`(`index`,"+
														   " `title_th`, `title_en`, `name_th`, `name_en`, `surname_th`, "+
														   " `surname_en`, `date`) "+
														   " VALUES ('"+key_index+"',"+"'"+title_th+"', " +"'"+title_en+"', "+
														   " '"+name_th+"', "+"'"+name_en+"', "+ "'"+surname_th+"', "+"'"+surname_en+"', "+
														   " '"+date_now+"')");
												
												PreparedStatement pstmt = connect.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);  
												pstmt.executeUpdate();  
												ResultSet keys = pstmt.getGeneratedKeys();    
												keys.next();  
												int key = keys.getInt(1);
												
												full_name_id = Integer.toString(key);
												 
												// log file
												String log = "admin import lecturer";						
												Log.writeAdminFile(log);
												Log.writeAdminFile(sql);
												 
												full_name_added ++;
												System.out.println("Phase A Success with full_name_id :"+ key);
												
											
											}catch(Exception e){
												System.out.println("Error At Phase A Add Full_name:"+e);
												error_log.add("row:"+num_row+": Phase A Error");
												
											
											} 
											////////////////////////////// End Phase A
											
											
											
											
											
											////////////// Phase B Add to people table 
											System.out.println("Data before Add People Table");	
											System.out.println("key_index:"+key_index);
											
											System.out.println("ful_name_id:"+full_name_id);
											System.out.println("gender:"+gender);
											
											System.out.println("date_of_birth"+birthdate.toString());
											System.out.println("phonenumber"+tel);
											System.out.println("address"+address);
											System.out.println("email"+email);
											System.out.println("facebook"+facebook);
											
											
										try{
											String sql = " INSERT INTO "+"`people`(`index`, `full_name_id`,"+
													   " `gender`, `date_of_birth`, `phone_number`,"+
													   " `address`, `email`, `facebook`)"+
													   " VALUES ('"+key_index+"', '"+full_name_id+"', '"+gender+"', "+
													    birthdate+", '"+tel+"', "+
													   " '"+address+"', '"+email+"', '"+facebook+"')";
											
											connect.createStatement().executeUpdate(sql);
											
											// log file
											Log.writeAdminFile(sql);
											
											System.out.println("Phase B Success");
											people_added ++;
											
										}catch(Exception y){
											System.out.println("Error At Phase B Add People:"+y);
											error_log.add("row:"+num_row+": Phase B Error");
												
										}////////////////End Phase B
										
										///////////////////Phase C Check education_postition 
										String sql_cmd_phase_c = " SELECT education_position_id "
												+ " FROM education_position "
												+ " WHERE education_position_name='"+edu_position+"'";
										
										try{
										
											ResultSet rs_edu =	connect.createStatement().executeQuery(" SELECT education_position_id "
													+ " FROM education_position "
													+ " WHERE education_position_name='"+edu_position+"'");
											
											rs_edu.last();
											if(rs_edu.getRow() == 1) {
												
												edu_position_id =  rs_edu.getString("education_position_id");
												
												
											}else{
												//education_position not found !!
												System.out.println("edu_pos not found : "+ sql_cmd_phase_c);
												throw new Exception();
												
												
											}
										}catch(Exception m){
											System.out.println("Phase C Error :"+m);
											error_log.add("row:"+num_row+": Phase C Error :"+m+": "+sql_cmd_phase_c);
											
										}
											
										///////////////////END Phase C
										
										/////////////////Phase D Check Management_position
										String sql_cmd_phase_d = " SELECT management_position_id "
														+ " FROM management_position "
														+ " WHERE management_position_name='"+management_position+"'";
												
										try{
												System.out.println("Phase D Check Management Position");
												System.out.println("management_position:"+management_position+":");
												
												if(("-").equals(management_position))
												{
													management_position_id = null;
													
													
												}else{
													//Get Managemenr_position_id
													ResultSet rs_man =	connect.createStatement().executeQuery(" SELECT management_position_id "
															+ " FROM management_position "
															+ " WHERE management_position_name='"+management_position+"'");
													
													rs_man.last();
													if(rs_man.getRow() == 1) {
														
														management_position_id =  rs_man.getString("management_position_id");
													}else{
														// management_position not found
														System.out.println("Phase D man_pos not found :"+sql_cmd_phase_d);
														throw new Exception();
													}
													
													
													
													
												}
										}catch(Exception d){
											
											System.out.println("Phase D Error:"+d);
											error_log.add("row:"+num_row+": Phase D Error :"+d+": " + sql_cmd_phase_d);
											
										}
										////////////////////END Phase D
										//Phase E get department_id from department table
									
										try{
											System.out.println("Phase 5 department_str_in:"+department_str_in);
											ResultSet rs_department = connect.createStatement().executeQuery(" SELECT department_id"
													+ " FROM department "
													+ " WHERE department_name_short='" + department_str_in +"'" );
											rs_department.last();
											if(rs_department.getRow() == 1) {
												department_id  = rs_department.getString("department_id");
												
												System.out.println("Phase E Found Department_id:"+department_id);
												
											}else{
												System.out.println("Phase E error");
												
												throw new Exception();
													
												
											}
											
											
										}catch(Exception q){ 
											System.out.println("Error occer at Phase E:"+q);
											error_log.add("row:"+num_row+": Phase E Error");
										}
										
										//End Phase E
										
										try{
										///////////Phase F Add Lecturer
										System.out.println("key_index:"+key_index);
										System.out.println("faculty_id:"+faculty_id);
										System.out.println("department_id:"+department_id);
										System.out.println("status:"+status);
										System.out.println("edu_pos_id:"+edu_position_id);
										System.out.println("man_pos_id:"+management_position_id);
										
										String sql = " INSERT INTO `lecturer`(`index`,`faculty_id`,`department_id`,"+
												" `status`, `education_position_id`,`management_position_id`)"+
												" VALUES ('"+key_index+"', "+faculty_id+", "+department_id+","+
												" '"+status+"', "+edu_position_id+", "+management_position_id+")";
										
										connect.createStatement().executeUpdate(sql);
												
										// log file
										Log.writeAdminFile(sql);
										
										System.out.println("Phase F Success");
										lecturer_added ++;
												
										}catch(Exception nm){
										
											System.out.println("Phase F error:"+nm);
											error_log.add(""+num_row+": Phase F Error");
																						
										}
										///////////////////End Phase F		
										

					
								}//end else not existing people
										
		                   			
		                   			
		                   			
		                   			
		                   			
		                   			
		                   			
            	} catch(Exception getData){ //end try Row row = rowIterator.next();
            		System.out.println("Error cant't read Excel:"+getData);
            
            		num_col = 0;
            		fail_list.add(num_row+1+"");
            		num_row++;
            	}
              
           
               	
            }// end while while(rowIterator.hasNext())
            file.close();
            System.out.println("fail_list size :"+fail_list.size());
            for(int num_fail= 0;num_fail<fail_list.size();num_fail++)
            {
            	System.out.println("Fail to import row:"+fail_list.get(num_fail).toString());
            }
            for(int temp_num = 0;temp_num<error_log.size();temp_num++)
            {
            	System.out.println(error_log.get(temp_num).toString());
            }
            System.out.println("full_name_added:"+full_name_added);
            System.out.println("people_added:"+people_added);
            System.out.println("lecturer_added:"+lecturer_added);
           
            String fail_list_all ="";
            if(fail_list.size()==0)
            {
            	fail_list_all ="";
            }
            else{
            	  fail_list_all = "ข้อมูลผิดพลาดในแถวที่ :";
            	  for(int x=0 ;x<fail_list.size();x++)
                  {
            		 
                  	fail_list_all = fail_list_all+fail_list.get(x).toString()+" , ";
                  }
            	  fail_list_all = fail_list_all + " \n"+ "กรุณาตรวจสอบข้อมูลดังกล่าว ก่อนนำเข้าอีกครั้ง";
            }
          
			out.print("success:\n"
					+people_added+" to peopel table.  \n"
					+ lecturer_added +" to lecturer table.  \n"
					+ full_name_added + " to full name table.  \n"
					+fail_list_all);
          	  	
			
		}//end try fileinputstream
		catch(Exception x){
			System.out.println(x);
			out.print("fail");
			
		}

		
		connect.close();

%>